Imports System.Text
Imports System.Drawing.Printing

Friend Class frmMain

    Private DT As New DataTable
    Private DBU As DataBaseUtility
    Private EH As ErrorHandler
    Private TH As TextHandler
    Private DefaultSQL As String
    Private PrintedSQL As String

    Private CreateTableSQL1 As String
    Private CreateTableSQL2 As String
    Private CreateTableSQL3 As String

    Private UP_MaxBatchNumSQL As String
    Private UP_MaxItemNumSQL As String
    Private UP_MaxPONumSQL As String

    Private P_MaxBatchNumSQL As String
    Private P_MaxItemNumSQL As String
    Private P_MaxPONumSQL As String

    Private UP_MinBatchNumSQL As String
    Private UP_MinItemNumSQL As String
    Private UP_MinPONumSQL As String

    Private P_MinBatchNumSQL As String
    Private P_MinItemNumSQL As String
    Private P_MinPONumSQL As String

    Private Const TimeOut = 300 'seconds
    Private lngCount As Long

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        DBU = New DataBaseUtility
        EH = New ErrorHandler
        TH = New TextHandler

        'Assign a delegate to supress non-numeric character entry in a text box
        AddHandler txtLotFrom.KeyPress, AddressOf FieldKeyPress
        AddHandler txtLotTo.KeyPress, AddressOf FieldKeyPress
        AddHandler txtBatchFrom.KeyPress, AddressOf FieldKeyPress
        AddHandler txtBatchTo.KeyPress, AddressOf FieldKeyPress

        'Assign a delegate to handle checkbox clicks
        AddHandler chkBarCode.CheckedChanged, AddressOf CheckedChanged
        AddHandler chkFinishing.CheckedChanged, AddressOf CheckedChanged

        CreateTableSQL1 = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'EQP_BATCHLOG') CREATE TABLE EQP_BATCHLOG (TabDelKeyValues varchar(200) not null PRIMARY KEY, InDate datetime null, BatchNum nvarchar(32) null, U_LotN0 nvarchar(10) null, ItemCode nvarchar(20) null, ItemName nvarchar(100) null, U_RepackOrder nvarchar(10) null, WhsCode nvarchar(8) null, U_Pieces int null, U_TotalQty numeric(19, 6), U_Grade nvarchar(10) null, PrintStatus nvarchar(1) null)"

        CreateTableSQL2 = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'EQP_BATCHPACK') " & _
                "CREATE TABLE EQP_BATCHPACK " & _
                "(	" & _
                "ID int identity(1, 1) PRIMARY KEY, " & _
                "TabDelKeyValues varchar(200) not null FOREIGN KEY (TabDelKeyValues) REFERENCES EQP_BATCHLOG(TabDelKeyValues) ON DELETE CASCADE, " & _
                "RollNum nvarchar(32) null, " & _
                "Insp varchar(20) null, " & _
                "CACD varchar(20) null, " & _
                "Yards varchar(20) null, " & _
                "Eigths varchar(20) null, " & _
                "ColorName varchar(20) null)"

        CreateTableSQL3 = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'EQP_BATCHINC') " & _
            "CREATE TABLE EQP_BATCHINC " & _
            "(ItemType varchar(1) not null PRIMARY KEY, " & _
            "LastNum int null) " & _
            "IF NOT EXISTS (SELECT * FROM EQP_BATCHINC WHERE ItemType = 'C') " & _
                "INSERT INTO EQP_BATCHINC (ItemType, LastNum) VALUES ('C', '0') " & _
            "IF NOT EXISTS (SELECT * FROM EQP_BATCHINC WHERE ItemType = 'F') " & _
                "INSERT INTO EQP_BATCHINC (ItemType, LastNum) VALUES ('F', '99999')"

        'UP = Unprinted batches
        UP_MaxBatchNumSQL = "SELECT Max(T0.BatchNum) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        UP_MaxItemNumSQL = "SELECT Max(T0.ItemCode) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        UP_MaxPONumSQL = "SELECT Max(T0.U_LotN0) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"

        'P = Printed Batches
        P_MaxBatchNumSQL = "SELECT Max(T0.BatchNum) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        P_MaxItemNumSQL = "SELECT Max(T0.ItemCode) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        P_MaxPONumSQL = "SELECT Max(T0.U_LotN0) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"

        UP_MinBatchNumSQL = "SELECT Min(T0.BatchNum) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        UP_MinItemNumSQL = "SELECT Min(T0.ItemCode) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        UP_MinPONumSQL = "SELECT Min(T0.U_LotN0) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus IS NULL AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"

        P_MinBatchNumSQL = "SELECT Min(T0.BatchNum) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        P_MinItemNumSQL = "SELECT Min(T0.ItemCode) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"
        P_MinPONumSQL = "SELECT Min(T0.U_LotN0) FROM EQP_BATCHLOG T0 WHERE (T0.PrintStatus = 'T' AND T0.ItemCode LIKE 'F%' AND T0.U_LotN0 IS NOT NULL)"

    End Sub

    Private Function BuildFilteredSQL(ByVal ItemCode As String, ByVal BatchNum As String, ByVal WH As String) As String
        Dim SQLSTR As String
        SQLSTR = "SELECT T0.InDate As 'In Date', T0.BatchNum As 'Batch Number', T0.U_LotN0 As 'Lot Number', T0.ItemCode As 'Item Code', T0.ItemName As 'Item Name', T0.U_RepackOrder As 'Repack Order' FROM OIBT T0 WHERE (T0.ItemCode = '" & ItemCode & "' AND T0.BatchNum = '" & BatchNum & "' AND T0.WhsCode = '" & WH & "') AND (T0.ItemCode BETWEEN '" & txtItemFrom.Text & "' And '" & txtItemTo.Text & "') AND (T0.BatchNum BETWEEN '" & txtBatchFrom.Text & "' And '" & txtBatchFrom.Text & "') AND (T0.U_LotN0 BETWEEN '" & txtLotFrom.Text & "' And '" & txtLotTo.Text & "')"
        Return SQLSTR
    End Function

    Private Sub frmMain_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        DBU.Disconnect()
    End Sub

    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Try

            Splash.Show()
            Splash.Refresh()

            Splash.lblLoadMsg.Text = "Check existance of dependancy tables..."
            Splash.Refresh()

            'Create the tables used for this app if they don't exist
            DBU.CreateTable(CreateTableSQL1)
            DBU.CreateTable(CreateTableSQL2)
            DBU.CreateTable(CreateTableSQL3)

            Splash.lblLoadMsg.Text = "Syncronizing data..."
            Splash.Refresh()
            Dim AL_TabDel As New ArrayList
            'Get Tab Delimited strings from the EQP_BatchLog table that do not have complete data from OIBT
            AL_TabDel = DBU.GetTabDelimitedStrings
            'Split the key values, perform single query to add the appropriate row in the EQP_BATCHLOG Table
            DBU.SynchWithOIBT(AL_TabDel)

            Splash.lblLoadMsg.Text = "Removing greige good items from EQP_BATCHLOG table..."
            Splash.Refresh()
            DBU.DeleteGreigeGoodsFromLog()

            Splash.lblLoadMsg.Text = "Setting default filters..."
            Splash.Refresh()
            ''Set the default filter values
            FillFieldsWithDefaultVals()

            Splash.lblLoadMsg.Text = "Loading data..."
            Splash.Refresh()
            'Select * FROM EQP_BatchLog into datatable, bind DataGridView1 to datatable
            Dim DT As New DataTable
            DT = DBU.GetBatchLogData(txtBatchFrom.Text, txtBatchTo.Text, txtItemFrom.Text, txtItemTo.Text, txtLotFrom.Text, txtLotTo.Text, "IS NULL")
            DataGridView1.DataSource = DT

            Splash.lblLoadMsg.Text = "Checking installed printers..."
            Splash.Refresh()
            LoadPrintersToCombos()

            Splash.lblLoadMsg.Text = "Setting default printers..."
            Splash.Refresh()

            LoadPrinterSettings()

            Splash.Close()

            lblCountDown.Text = Nothing
            tmrMain.Enabled = True
            tmrMain.Start()

        Catch ex As Exception
            DBU.Disconnect()
            Splash.lblLoadMsg.Text = "Logging critical error..."
            EH.RecordError(ex)
            System.Threading.Thread.Sleep(2000)
            Splash.Close()
            MsgBox("A critical error has occurred and has been logged.  " & ex.Message, MsgBoxStyle.Critical, "Batch Code Printing")
        End Try

    End Sub

    Friend Sub LoadPrintersToCombos()

        Dim Printer As String

        For Each Printer In PrinterSettings.InstalledPrinters
            cmbBarCode.Items.Add(Printer)
            cmbFinishing.Items.Add(Printer)
        Next

    End Sub

    Private Sub LoadPrinterSettings()

        Try

            Dim AL As New ArrayList
            AL = TH.ReadFileLinesToArrayList("PrintSettings.ini")

            'If no printer settings are in the ini file, skip load.  Settings will be saved when
            'the first print job is done, or when a checkbox is checked.

            If AL.Count = 0 Then
                Exit Sub
            End If

            If AL.Item(0) = "True" Then
                chkBarCode.Checked = True
            Else
                chkBarCode.Checked = False
            End If

            If AL.Item(1) = "True" Then
                chkFinishing.Checked = True
            Else
                chkFinishing.Checked = False
            End If

            cmbBarCode.SelectedIndex = AL.Item(2)
            cmbFinishing.SelectedIndex = AL.Item(3)

        Catch ex As Exception

        End Try
        

    End Sub

    Private Sub SavePrinterSettings()

        Dim SB As New StringBuilder

        If chkBarCode.Checked = True Then
            SB.Append("True")
        Else
            SB.Append("False")
        End If

        SB.Append(", ")

        If chkFinishing.Checked = True Then
            SB.Append("True")
        Else
            SB.Append("False")
        End If

        SB.Append(", ")

        SB.Append(cmbBarCode.SelectedIndex)

        SB.Append(", ")

        SB.Append(cmbFinishing.SelectedIndex)

        TH.WriteLineToFile(SB.ToString, "PrintSettings.ini", True)

    End Sub

    Private Sub mnuClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuClose.Click
        End
    End Sub

    Private Sub FieldKeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If Char.IsDigit(e.KeyChar) = True Or Char.IsControl(e.KeyChar) = True Then
            e.Handled = False
        Else
            e.Handled = True
        End If
    End Sub

    Private Sub FillFieldsWithDefaultVals()

        'Default all the filters to min/max values in the database.

        If TextBoxHasText(txtLotFrom) = False And chkPrinted.Checked = False Then
            txtLotFrom.Text = DBU.PerformScalarQuery(UP_MinPONumSQL)
        ElseIf TextBoxHasText(txtLotFrom) = False And chkPrinted.Checked = True Then
            txtLotFrom.Text = DBU.PerformScalarQuery(P_MinPONumSQL)
        End If

        If TextBoxHasText(txtLotTo) = False And chkPrinted.Checked = False Then
            txtLotTo.Text = DBU.PerformScalarQuery(UP_MaxPONumSQL)
        ElseIf TextBoxHasText(txtLotTo) = False And chkPrinted.Checked = True Then
            txtLotTo.Text = DBU.PerformScalarQuery(P_MaxPONumSQL)
        End If

        If TextBoxHasText(txtItemFrom) = False And chkPrinted.Checked = False Then
            txtItemFrom.Text = DBU.PerformScalarQuery(UP_MinItemNumSQL)
        ElseIf TextBoxHasText(txtItemFrom) = False And chkPrinted.Checked = True Then
            txtItemFrom.Text = DBU.PerformScalarQuery(P_MinItemNumSQL)
        End If

        If TextBoxHasText(txtItemTo) = False And chkPrinted.Checked = False Then
            txtItemTo.Text = DBU.PerformScalarQuery(UP_MaxItemNumSQL)
        ElseIf TextBoxHasText(txtItemTo) = False And chkPrinted.Checked = True Then
            txtItemTo.Text = DBU.PerformScalarQuery(P_MaxItemNumSQL)
        End If

        If TextBoxHasText(txtBatchFrom) = False And chkPrinted.Checked = False Then
            txtBatchFrom.Text = DBU.PerformScalarQuery(UP_MinBatchNumSQL)
        ElseIf TextBoxHasText(txtBatchFrom) = False And chkPrinted.Checked = True Then
            txtBatchFrom.Text = DBU.PerformScalarQuery(P_MinBatchNumSQL)
        End If

        If TextBoxHasText(txtBatchTo) = False And chkPrinted.Checked = False Then
            txtBatchTo.Text = DBU.PerformScalarQuery(UP_MaxBatchNumSQL)
        ElseIf TextBoxHasText(txtBatchTo) = False And chkPrinted.Checked = True Then
            txtBatchTo.Text = DBU.PerformScalarQuery(P_MaxBatchNumSQL)
        End If

        DBU.Disconnect()

    End Sub

    Private Sub tsbPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbPrint.Click

        Try

            Dim P As New Printer
            Dim DS As New DataSet
            Dim DGVRow As New DataGridViewRow
            Dim ItemCode As String, BatchNum As String, WH As String, LotNo As String

            'user can only select one row at a time
            If DataGridView1.SelectedRows.Count = 0 Then
                Throw New Exception("No batches selected for printing.")                
            End If

            If DataGridView1.SelectedRows.Count > 1 Then
                Throw New Exception("Select only one row at a time for printing.")                                
            End If

            'Must select at least one item
            If cmbBarCode.SelectedItem = Nothing And chkBarCode.Checked = True Then
                Throw New Exception("No printer selected to print the bar code.")
            End If

            'Must have a printer selected for each document that is checked for printing
            If cmbFinishing.SelectedItem = Nothing And chkFinishing.Checked = True Then
                Throw New Exception("No printer selected to print the finishing document.")
            End If

            'Get info on the first selected row
            DGVRow = DataGridView1.SelectedRows.Item(0)

            BatchNum = DGVRow.Cells(1).Value.ToString
            ItemCode = DGVRow.Cells(3).Value.ToString
            WH = "01"
            LotNo = DGVRow.Cells(2).Value.ToString

            'save the printer settings each time a print command takes place
            SavePrinterSettings()

            If chkBarCode.Checked = True Or chkFinishing.Checked = True Then

                'Create a dataset to be passed by ref in all printing docs
                DS = DBU.GetDataForDocuments(ItemCode, BatchNum, WH, LotNo)

                If chkBarCode.Checked = True Then
                    P.PrintBarcodeLabel(cmbBarCode.SelectedItem.ToString, DS)
                End If

                If chkFinishing.Checked = True Then
                    P.PrintFinishingDoc(cmbFinishing.SelectedItem.ToString, DS)
                End If

                DBU.UpdatePrintedRecord(ItemCode, BatchNum, WH)

            Else

                Throw New Exception("No printer selected for printing.")

            End If

            ClearAllFields()
            FillFieldsWithDefaultVals()
            RefreshData()

        Catch ex As Exception

            EH.RecordError(ex, True)

        End Try

    End Sub

    'Checks if a text box is blank or not
    Private Function TextBoxHasText(ByVal obj As TextBox) As Boolean
        If Not obj.Text = Nothing Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Sub ConnectionToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ConnectionToolStripMenuItem.Click
        frmSettings.Show()
    End Sub

    Private Sub ClearAllFields()
        txtLotFrom.Text = Nothing
        txtLotTo.Text = Nothing
        txtItemFrom.Text = Nothing
        txtItemTo.Text = Nothing
        txtBatchFrom.Text = Nothing
        txtBatchTo.Text = Nothing
    End Sub

    Private Sub RefreshData()

        Try

            Dim PrintStatus As String

            Dim AL_TabDel As New ArrayList
            'Get Tab Delimited strings from the EQP_BatchLog table that do not have complete data from OIBT
            AL_TabDel = DBU.GetTabDelimitedStrings
            'Split the key values, perform single query to add the appropriate row in the EQP_BATCHLOG Table
            DBU.SynchWithOIBT(AL_TabDel)

            'Select * FROM EQP_BatchLog into datatable, bind DataGridView1 to datatable
            Dim DT As New DataTable

            Select Case chkPrinted.Checked
                Case True
                    PrintStatus = "= 'T'"
                Case False
                    PrintStatus = "IS NULL"
                Case Else
                    PrintStatus = "IS NULL"
            End Select

            DT = DBU.GetBatchLogData(txtBatchFrom.Text, txtBatchTo.Text, txtItemFrom.Text, txtItemTo.Text, txtLotFrom.Text, txtLotTo.Text, PrintStatus)
            DataGridView1.DataSource = DT

        Catch ex As Exception

            DBU.Disconnect()
            Throw New Exception(ex.Message)

        End Try

    End Sub

    Private Sub DataGridView1_RowHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick
        If DataGridView1.SelectedRows.Count > 1 Then
            MsgBox("Select only one row at a time for printing.", MsgBoxStyle.Information, "Bar Code Printing")
            DataGridView1.Refresh()
        End If
    End Sub

    Private Sub chkPrinted_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkPrinted.CheckedChanged

        Try
            If Me.BackColor = Color.AliceBlue Then
                Me.BackColor = Color.LightGreen
            Else
                Me.BackColor = Color.AliceBlue
            End If
            ClearAllFields()
            FillFieldsWithDefaultVals()
            RefreshData()
        Catch ex As Exception
            EH.RecordError(ex, True)
        End Try

    End Sub

    Private Sub tsbFilters_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbFilters.Click
        Try
            ClearAllFields()
            FillFieldsWithDefaultVals()
        Catch ex As Exception
            EH.RecordError(ex, True)
        End Try        
    End Sub

    Private Sub tsbRefresh_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles tsbRefresh.Click

        Try
            If tsmRefreshFilters.Checked = True Then
                ClearAllFields()
                FillFieldsWithDefaultVals()
            End If
            RefreshData()
        Catch ex As Exception
            EH.RecordError(ex, True)
        End Try

    End Sub

    Private Sub CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

        Try
            SavePrinterSettings()
        Catch ex As Exception
            EH.RecordError(ex)
        End Try

    End Sub

    Private Sub DataGridView1_RowHeaderMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseDoubleClick

        Dim DGVRow As New DataGridViewRow
        TH = New TextHandler

        DGVRow = DataGridView1.SelectedRows.Item(0)
        Dim Line As String = DGVRow.Cells(3).Value.ToString & ", " & DGVRow.Cells(1).Value.ToString & ", " & "01"

        'Save the key vals for the frmPackaging form to pickup
        TH.WriteLineToFile(Line, "Packaging.ini", True)

        frmPackaging.Show()
        frmPackaging.Focus()

    End Sub

    Private Sub tmrMain_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles tmrMain.Tick


        lngCount += 1
        Dim Cnt As Long = (TimeOut - lngCount)

        lblCountDown.Text = "Auto update in " & Cnt.ToString & " seconds."

        If lngCount = TimeOut Then

            tmrMain.Enabled = False
            tmrMain.Stop()
            lblCountDown.Text = "Updating..."

            ClearAllFields()
            FillFieldsWithDefaultVals()
            RefreshData()

            lngCount = 0

            tmrMain.Enabled = True
            tmrMain.Start()

        End If

    End Sub

    Private Sub tsmRefreshFilters_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsmRefreshFilters.Click
        If tsmRefreshFilters.Checked Then
            tsmRefreshFilters.Checked = False
        Else
            tsmRefreshFilters.Checked = True
        End If
    End Sub
End Class
